1) Make SQL Data Types Super Clear - From the Absolute Basics to the Advanced Level

Data Types in SQL 

Alright, let’s make SQL data types super clear, from beginner to advanced and with lots of examples so you don’t just memorize them, you understand when and why to use each one.

1. What are Data Types in SQL?

Data types tell the database what kind of data can be stored in a column.
Think of them like labels on containers: a box labeled “apples” should not hold “carrots”.

2. Main Categories of SQL Data Types

Different SQL flavors (MySQL, PostgreSQL, SQL Server, Oracle) have slightly different names and ranges,
but the main categories are:

Category	Purpose	Examples
Numeric	Store numbers	INT, BIGINT, DECIMAL, FLOAT
String / Character	Store text	CHAR, VARCHAR, TEXT
Date / Time	Store date and/or time	DATE, DATETIME, TIMESTAMP
Boolean	Store true/false	BOOLEAN
Binary	Store raw data like images	BLOB, VARBINARY
Others	Special types	JSON, XML, ARRAY, ENUM

    

3. Beginner Level - Core Data Types

A. Numeric Types

1) INT (Integer)

• Whole numbers, no decimal
• Range depends on DB (MySQL -2,147,483,648 to 2,147,483,647 for INT)

• 
      CREATE TABLE products (
      	product_id INT,
      	quantity INT
  	);
  
  	INSERT INTO products VALUES (1, 50);  -- OK
  	INSERT INTO products VALUES (2, 5.5); -- ❌ Error in strict mode
  
      

2) DECIMAL(p, s) or NUMERIC(p, s)

• Fixed-point number
• p = total digits, s = digits after decimal
• Great for currency (avoids rounding issues of FLOAT)

3) FLOAT / REAL / DOUBLE

• Floating-point numbers (approximate values)
• Faster but less precise, not good for money.

B. String Types

1) CHAR(n)

• Fixed length, pads with spaces if shorter
• Good for fixed-length codes like country codes

2) VARCHAR(n)

• Variable length, up to n characters
• Most common for general text

3) TEXT

• Large text blocks (no length limit in some DBs)
• Cannot always be indexed efficiently

C. Date/Time Types

1) DATE

• Stores year, month, day (YYYY-MM-DD)
• Example:

    birth_date DATE; -- '2025-08-12'

2) DATETIME / TIMESTAMP

• Stores both date and time
• TIMESTAMP often auto-updates to current time.

3) TIME

• Stores time only (HH:MM:SS)

D. Boolean

• Stores TRUE or FALSE (often stored internally as 0 and 1)
• Example:  is_active BOOLEAN; -- 1 or 0

4. Intermediate Level: Special & Less Common Types

A. Binary Types

• Store files, images, audio in binary format
• Example:

    profile_picture BLOB; -- Binary Large Object

    

B. Enumerated Types

• Predefined set of allowed values
• Example:

    status ENUM('Pending', 'Shipped', 'Delivered', 'Cancelled');

C. JSON

• Store structured JSON data (PostgreSQL, MySQL 5.7+)
• Example:

    details JSON; 
    -- Example: {"color": "red", "size": "M"}

5. Advanced Level: DB-Specific & Complex Types

A. Arrays (PostgreSQL)

    tags TEXT[]; -- Array of strings

B. XML (SQL Server, PostgreSQL)

    config XML;

C. Spatial / Geographic Types

• Store coordinates, shapes, maps

    location POINT;  -- Stores latitude & longitude

    

6. Choosing the Right Data Type (Best Practices)

✅ Use the smallest type that fits your data (saves storage, improves speed)
✅ Use DECIMAL for currency, not FLOAT
✅ Use DATE / DATETIME instead of strings for dates
✅ Avoid TEXT for small strings — use VARCHAR
✅ Use ENUM/constraints for fixed sets of values

7. Quick Practical Example

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    join_date DATE DEFAULT CURRENT_DATE,
    salary DECIMAL(10,2),
    is_active BOOLEAN DEFAULT TRUE,
    skills JSON
);

INSERT INTO employees (emp_id, name, salary, skills)
VALUES
(1, 'John Doe', 50000.75, '{"languages":["SQL","Python"],"level":"Senior"}');

8. A Complete Summary Table for Beginner → Advanced SQL Data Types (with usage and examples)


9. Beginner → Advanced SQL Data Types Quiz
A. Beginner (Easy)
True/False:
The VARCHAR(50) type always stores exactly 50 characters.
Which data type would you use for storing product prices?
a) FLOAT
b) DECIMAL(10,2)
c) INT
Write a query to create a table students with:
id as integer
name as up to 30 characters
enrolled_date as date
What’s wrong with this insert?


Which is better for storing a fixed 2-letter country code?
a) VARCHAR(2)
b) CHAR(2)
B. Intermediate (Medium)
Create a table products where:
id is integer primary key
title is up to 100 chars
description is large text
price is DECIMAL(8,2)
True/False: TEXT columns can always be indexed the same way as VARCHAR.
Which data type is best for storing binary image data?
a) BLOB
b) VARCHAR
c) TEXT
Write a query to create a table employees with:
emp_id integer
status ENUM of ('Active','Inactive','On Leave')
What happens if you insert '123.45' into an INT column?
C. Advanced (Hard)
Create a table logs with a JSON column event_data.
Which data type would you choose to store GPS coordinates? Why?
True/False: DECIMAL is more precise than FLOAT for storing monetary values.
Write a query in PostgreSQL to store a list of skills as an array of text values.
Explain why using CHAR(255) for all text columns can cause performance and storage issues.
Answers + Explanations
Beginner
False – VARCHAR(50) stores up to 50 characters, not exactly 50.
b) DECIMAL(10,2) – For currency, fixed precision avoids rounding errors.

Problem: '12-08-2025' format may not match SQL’s default 'YYYY-MM-DD'. Should use '2025-08-12'.
b) CHAR(2) – Faster for fixed-length codes.

Intermediate


False – TEXT columns have indexing limitations in many DBs.
a) BLOB – Designed for binary data.


It will store only 123 (fractional part discarded).
Advanced


POINT – Optimized for spatial operations (PostGIS in PostgreSQL, spatial in MySQL).
True – DECIMAL stores exact numbers; FLOAT is approximate.


CHAR(255) always stores fixed 255 chars, wasting space and increasing I/O for short strings.

Next Part: Normalization and Denormalization